﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_employee_master
{
    public DAL_tbl_employee_master()
    {

    }



    public List<tbl_employee_master> select(String USER_CODE)
    {
        List<tbl_employee_master> list = new List<tbl_employee_master>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,USER_ROLE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID,DESIGNATION_ID,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_employee_master where USER_CODE=@USER_CODE";
        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_employee_master()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                USER_ROLE_ID = Convert.ToInt32(dr["USER_ROLE_ID"]),
                FIRST_NAME = dr["FIRST_NAME"].ToString(),
                LAST_NAME = dr["LAST_NAME"].ToString(),
                DEPARTMENT_ID = Convert.ToInt32(dr["DEPARTMENT_ID"]),
                DESIGNATION_ID = Convert.ToInt32(dr["DESIGNATION_ID"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_employee_master> selectAll()
    {
        List<tbl_employee_master> list = new List<tbl_employee_master>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select USER_CODE,USER_ROLE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID,DESIGNATION_ID,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_employee_master Order By USER_CODE";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_employee_master()
            {
                USER_CODE = dr["USER_CODE"].ToString(),
                USER_ROLE_ID = Convert.ToInt32(dr["USER_ROLE_ID"]),
                FIRST_NAME = dr["FIRST_NAME"].ToString(),
                LAST_NAME = dr["LAST_NAME"].ToString(),
                DEPARTMENT_ID = Convert.ToInt32(dr["DEPARTMENT_ID"]),
                DESIGNATION_ID = Convert.ToInt32(dr["DESIGNATION_ID"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"select em.USER_CODE,rm.ROLE_NAME as USER_ROLE_ID,em.FIRST_NAME,em.LAST_NAME,dm.DEPARTMENT_NAME as DEPARTMENT_ID,dgm.DESIGNATION_NAME as DESIGNATION_ID
,em.CREATED_BY,em.CREATED_DATE,em.UPDATED_BY,em.UPDATED_DATE
 from kanyogendra_inventory.tbl_employee_master em 
LEFT JOIN kanyogendra_inventory.tbl_role_master rm
on rm.ROLE_ID=em.USER_ROLE_ID
LEFT JOIN kanyogendra_inventory.tbl_department_master dm
on dm.DEPARTMENT_ID =em.DEPARTMENT_ID
LEFT JOIN kanyogendra_inventory.tbl_designation_master dgm
on dgm.DESIGNATION_ID =em.DESIGNATION_ID Order By em.CREATED_DATE desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }


    public int insert(String USER_CODE, int USER_ROLE_ID, String FIRST_NAME, String LAST_NAME, int DEPARTMENT_ID, int DESIGNATION_ID, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_employee_master(USER_CODE, USER_ROLE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, DESIGNATION_ID, CREATED_BY, CREATED_DATE)
                        values(@USER_CODE, @USER_ROLE_ID, @FIRST_NAME, @LAST_NAME, @DEPARTMENT_ID, @DESIGNATION_ID, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@USER_ROLE_ID", USER_ROLE_ID);
        cmd.Parameters.AddWithValue("@FIRST_NAME", FIRST_NAME);
        cmd.Parameters.AddWithValue("@LAST_NAME", LAST_NAME);
        cmd.Parameters.AddWithValue("@DEPARTMENT_ID", DEPARTMENT_ID);
        cmd.Parameters.AddWithValue("@DESIGNATION_ID", DESIGNATION_ID);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(String USER_CODE, int USER_ROLE_ID, String FIRST_NAME, String LAST_NAME, int DEPARTMENT_ID, int DESIGNATION_ID, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_employee_master SET USER_CODE=@USER_CODE, USER_ROLE_ID=@USER_ROLE_ID, FIRST_NAME=@FIRST_NAME, LAST_NAME=@LAST_NAME, DEPARTMENT_ID=@DEPARTMENT_ID, DESIGNATION_ID=@DESIGNATION_ID, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);
        cmd.Parameters.AddWithValue("@USER_ROLE_ID", USER_ROLE_ID);
        cmd.Parameters.AddWithValue("@FIRST_NAME", FIRST_NAME);
        cmd.Parameters.AddWithValue("@LAST_NAME", LAST_NAME);
        cmd.Parameters.AddWithValue("@DEPARTMENT_ID", DEPARTMENT_ID);
        cmd.Parameters.AddWithValue("@DESIGNATION_ID", DESIGNATION_ID);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(String USER_CODE)
    {
        int i = 0;
        string sql = "Delete FROM tbl_employee_master WHERE USER_CODE=@USER_CODE";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@USER_CODE", USER_CODE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}